﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Model;
using BLL;
using MDP.WebOpenPortal.Models;
using System.Data;
using System.Data.SqlClient;
using Common;
using System.Web.Script.Serialization;
using System.Collections;
using System.Text;
using System.Net;

namespace MDP.WebOpenPortal.Controllers
{
    public class EtlSourceController : BaseController
    {
        private static EncrypCode encrypCode = new EncrypCode();


        public ActionResult Index(FormCollection collection)
        {
            #region 检查Url参数
            var urlRouteInfo = new Model.UrlRoute();
            try
            {
                urlRouteInfo = CheckUrlRoute("ETLSource");
            }
            catch (Exception ex)
            {
                Response.Write("操作错误！" + ex.Message);
                Response.End();
                return View();
            }
            #endregion

            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = urlRouteInfo.DBName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            var systemID = urlRouteInfo.EntityId;
            var sourceInfo = bll.GetEtlSourceSystemInfoById(systemID);
            EtlSourceModel sourceModel = new EtlSourceModel();
            sourceModel.EtlSourceInfo = sourceInfo;
            sourceModel.DbName = urlRouteInfo.DBName;

            string strContactList = string.Empty;
            IList<SysUserInfo> contactList = bll.GetEtlSourceUserList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            foreach (var contactItem in contactList)
            {
                if (urlRouteInfo.UserID == contactItem.Username)
                {
                    Session["MDP_UN_Open"] = contactItem.EmployeeName;
                }
                var userInfo = "工号:" + contactItem.EmployeeCode + "\n用户名:" + contactItem.Username + "\n公司:" + contactItem.CompanyName + "\n电话:" + contactItem.OfficeTel + "\nEmail:" + contactItem.OfficeEmail;
                strContactList += "<span class='s3 s3" + contactItem.Guid + "'>  <!--<input type='checkbox' value='" + contactItem.Username + "' />--><a title='" + userInfo + "'>" + contactItem.EmployeeName + "</a>  </span>";
            }
            sourceModel.StrContactList = strContactList;

            DictionaryBLL dictionaryBLL = new DictionaryBLL(loginInfos);
            var dictionaryList = dictionaryBLL.GetListByKeyName("SourceType");
            foreach (var dictionaryItem in dictionaryList)
            {
                sourceModel.EtlSourceTypes.Add(new SelectListItem()
                {
                    Text = dictionaryItem.Name,
                    Value = dictionaryItem.Value,
                    Selected = dictionaryItem.Value.Equals(sourceInfo.SourceType.ToString()) ? true : false
                });
            }
            return View(sourceModel);
        }
        public JsonResult GetEtlSourceUserList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            systemID = string.IsNullOrEmpty(systemID) ? "" : systemID;
            IList<SysUserInfo> list = bll.GetEtlSourceUserList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });

            return Json(list);
        }

        /// <summary>
        /// 保存源系统信息
        /// </summary>
        /// <param name="data">源系统信息</param>
        /// <param name="dbConfigs">连接字符串</param>
        /// <returns></returns>
        public string EtlSourceUpdate(EtlSourceSystemInfo data, FormCollection collection, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL sourcesystemBll = new EtlSourceSystemBLL(loginInfos);
            SysPublicBLL pbll=new SysPublicBLL(loginInfos);
            DBDeploy DBInfo = pbll.GetDBInfo(dbName);
            string DBType = DBInfo.DBType.ToString();
            var par = new SqlParameter[] { new SqlParameter("@SourceSysName", data.SourceSysName) };
            string result = "";
            string[] msg = { };
            try
            {
                if (string.IsNullOrEmpty(data.GUID) && sourcesystemBll.GetSourceSystemByName(par))
                {
                    return "2";
                }
                JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
                IList<ETLCallBackConfig> cbList = (IList<ETLCallBackConfig>)jsSerializer.Deserialize(collection["CBs"], typeof(IList<ETLCallBackConfig>));
                #region 检查HTTP回调的地址是否正常

                foreach (ETLCallBackConfig cbConfig in cbList)
                {
                    if (cbConfig.CallBackType.Equals(2))
                    {
                        string targetURL = cbConfig.CallBackName;
                        Hashtable param = new Hashtable();
                        string para = cbConfig.Paras;
                        string paraValues = cbConfig.ParaValues;
                        if (!string.IsNullOrEmpty(para))
                        {
                            List<string> paraArr = para.Split('&').ToList();
                            List<string> paraValuesArr = paraValues.Split('&').ToList();
                            for (int i = 0; i < paraArr.Count; i++)
                            {
                                //添加回调配置参数
                                param[paraArr[i]] = paraValuesArr[i];
                            }
                        }
                        string meghod = "POST";
                        var encoding = Encoding.UTF8;
                        var errorMessage = string.Empty;
                        var callResult = HttpUtils.IsExist(targetURL, param, meghod, encoding, (e) =>
                        {
                            return true;
                        }, (e) =>
                        {
                            var we = e as System.Net.WebException;
                            if (we != null)
                            {
                                var response = we.Response as System.Net.HttpWebResponse;
                                if (response != null)
                                {
                                    if (response.StatusCode == HttpStatusCode.NotFound)
                                    {
                                        errorMessage = e.Message;

                                        return false;
                                    }
                                }
                            }
                            //errorMessage = e.Message;
                            return true;
                        });
                        if (callResult == false)
                        {
                            return string.Format("回调地址 {0} 调用错误：{1}", targetURL, errorMessage);
                        }
                    }
                }

                #endregion

                 
                var sourceInfo = sourcesystemBll.GetEtlSourceSystemInfoById(data.GUID);
                data.SourceSysName = sourceInfo.SourceSysName;
                result = sourcesystemBll.Update2(data).ToString();
                new SysOperationLog(loginInfos).Log("源系统管理", Request.UserHostAddress, OperationAction.更新, OperationStatus.成功, "更新源系统信息操作,操作成功,ID=" + data.GUID, loginInfos.UserName);//Request.Cookies["MDM_UserName"].Value);

                if (result == "1")//保存成功，保存连接字符串和联系人
                {
                    #region 更新连接字符串
                    //已保存数据库连接串
                    IList<ETLDbConfig> oldDBlist = sourcesystemBll.GetETLDbConfigList(new IDataParameter[] { new SqlParameter("@SysID", data.GUID) });

                    //提交的数据库连接串
                    List<ETLDbConfig> conList = GetDatabaseConnections(collection);
                    foreach (ETLDbConfig etlDBInfo in oldDBlist)//删除移除的连接
                    {
                        if (conList.FindIndex(o => { return o.GUID == etlDBInfo.GUID; }) == -1)
                        {
                            sourcesystemBll.DeleteDbConfig(etlDBInfo);
                        }
                    }
                    for (int i = 0; i < conList.Count; i++)
                    {
                        ETLDbConfig dbConfigInfo = conList[i];
                        dbConfigInfo.DbSortID = i;
                        dbConfigInfo.SysID = data.GUID;
                        dbConfigInfo.DbType = data.SourceType;
                        dbConfigInfo.CreateUser = data.CreateUser ?? loginInfos.Dbuser;

                        //ETLDbConfig oldDBConfig = new ETLDbConfig();
                        //if (oldDBlist.Count > 0)
                        //    oldDBConfig = oldDBlist.First(o => { return o.DbDataSource == dbConfigInfo.DbDataSource; });
                        if (dbConfigInfo.GUID.Length == 36)
                        {
                            sourcesystemBll.UpdateDbConfig(dbConfigInfo);
                        }
                        else
                        {
                            dbConfigInfo.GUID = Guid.NewGuid().ToString();
                            sourcesystemBll.AddDbConfig(dbConfigInfo);
                            if (DBType == "2")//大数据更新标识配置表
                            {
                                var viewLists = sourcesystemBll.GetSourceViewConfigBySystemID(data.GUID);
                                if (viewLists.Count > 0)
                                {
                                    sourcesystemBll.UpdateBigDateTagConfig(data.GUID, dbConfigInfo.GUID);
                                }
                            }
                        }
                    }


                    #endregion
                    #region 通配符配置
                    List<ETLWDCConfig> wdcList = (List<ETLWDCConfig>)jsSerializer.Deserialize(collection["WDCs"], typeof(List<ETLWDCConfig>));//页面传值
                    IList<ETLWDCConfig> list = sourcesystemBll.GetWDCConfigList(new IDataParameter[] { new SqlParameter("@SysID", data.GUID) });//保存的记录
                    foreach (ETLWDCConfig item in list)//删除记录
                    {
                        if (wdcList.FindIndex(o => { return o.GUID == item.GUID; }) == -1)
                        {
                            sourcesystemBll.DeleteWDCConfig(item.GUID);
                        }
                    }
                    foreach (ETLWDCConfig wdcInfo in wdcList)
                    {
                        wdcInfo.SysID = data.GUID;
                        if (string.IsNullOrEmpty(wdcInfo.GUID))//添加记录
                        {
                            wdcInfo.GUID = Guid.NewGuid().ToString();
                            sourcesystemBll.AddWDCConfig(wdcInfo);
                        }
                        else
                            sourcesystemBll.UpdateWDCConfig(wdcInfo);//更新记录
                    }

                    #endregion
                    #region 回调配置
                    //删除旧记录,通配符,回调配置,联系人信息
                    if (sourcesystemBll.DelSysConfig(data.GUID))
                    {
                        foreach (ETLCallBackConfig cbConfig in cbList)
                        {
                            cbConfig.GUID = Guid.NewGuid().ToString();
                            cbConfig.SysType = 1;
                            cbConfig.SysID = data.GUID;
                            sourcesystemBll.AddCallBack(cbConfig);//添加回调配置
                            string para = cbConfig.Paras;
                            string paraValues = cbConfig.ParaValues;
                            if (!string.IsNullOrEmpty(para))
                            {
                                List<string> paraArr = para.Split('&').ToList();
                                List<string> paraValuesArr = paraValues.Split('&').ToList();
                                for (int i = 0; i < paraArr.Count; i++)
                                {
                                    ETLCallBackPara paras = new ETLCallBackPara()
                                    {
                                        GUID = Guid.NewGuid().ToString(),
                                        KeyName = paraArr[i],
                                        KeyValue = paraValuesArr[i],
                                        ParentGUID = cbConfig.GUID
                                    };
                                    sourcesystemBll.AddCallBackParas(paras);//添加回调配置参数
                                }
                            }
                        }
                    }
                    #endregion
                    #region 联系人信息
                    string contacts = collection["Users"];
                    if (!string.IsNullOrEmpty(contacts))
                    {
                        string[] contactArr = contacts.Split(',');
                        foreach (string contactID in contactArr)
                        {
                            EtlContactInfo contactInfo = new EtlContactInfo()
                            {
                                GUID = Guid.NewGuid().ToString(),
                                UserID = contactID,
                                SysID = data.GUID,
                                SysType = 1
                            };
                            sourcesystemBll.AddContact(contactInfo);
                        }
                    }
                    #endregion
                }
                result = result + "," + data.GUID;
            }
            catch (Exception ex)
            {
                result = "操作失败." + ex.ToString();
            }
            return result;
        }


        /// <summary>
        /// 取源系统连接字符串
        /// </summary>
        /// <param name="systemID"></param>
        /// <returns></returns>
        public JsonResult GetEtlSourceDbConfigList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            IList<ETLDbConfig> list = bll.GetETLDbConfigList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            List<ETLDbInfo> dbinfo = new List<ETLDbInfo>();
            foreach (ETLDbConfig db in list)
            {
                ETLDbInfo info = new ETLDbInfo()
                {
                    GUID = db.GUID,
                    Host = db.DbDataSource,
                    Server = db.DbDatabase,
                    User = db.DbUserID,
                    Pass = encrypCode.Decrypto(db.DbPassword),
                    Part = db.DbPort.ToString(),
                    SID = db.DbSID,
                    FileUrl = db.DbDataSource,
                    FileUser = db.DbUserID,
                    FilePass = encrypCode.Decrypto(db.DbPassword),
                    WildCard = db.WildCard,
                    ExtractOrder = db.ExtractOrder,
                    Status = db.Status,
                    Description=db.Description
                };
                dbinfo.Add(info);
            }
            jsonDict.Add("total", dbinfo.Count);
            jsonDict.Add("rows", dbinfo);
            return Json(jsonDict);
        }
        /// <summary>
        /// 取回调配置列表
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public JsonResult GetCallBackConfigList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            IList<ETLCallBackConfig> list = bll.GetCallBackConfigList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            foreach (ETLCallBackConfig model in list)
            {
                if (model.CallBackType == 2)
                {
                    IList<ETLCallBackPara> paras = bll.GetCallBackParasList(new IDataParameter[] { new SqlParameter("@ParentGUID", model.GUID) });
                    List<string> ps = new List<string>();
                    List<string> pvs = new List<string>();
                    foreach (ETLCallBackPara p in paras)
                    {
                        ps.Add(p.KeyName);
                        pvs.Add(p.KeyValue);
                    }
                    model.Paras = string.Join("&", ps);
                    model.ParaValues = string.Join("&", pvs);
                }
            }
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
            jsonDict.Add("total", list.Count);
            jsonDict.Add("rows", list);
            return Json(jsonDict);
        }
        /// <summary>
        /// 取通配符配置列表
        /// </summary>
        /// <param name="systemID"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public JsonResult GetWDCConfigList(string systemID, string dbName)
        {
            LoginInfo loginInfos = base.UserInfo;
            loginInfos.DBName = dbName;
            EtlSourceSystemBLL bll = new EtlSourceSystemBLL(loginInfos);
            IList<ETLWDCConfig> list = bll.GetWDCConfigList(new IDataParameter[] { new SqlParameter("@SysID", systemID) });
            Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();

            jsonDict.Add("total", list.Count);
            jsonDict.Add("rows", list);
            return Json(jsonDict);
        }

        /// <summary>
        /// 解析数据库字符串
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        private List<ETLDbConfig> GetDatabaseConnections(FormCollection collection)//, int sourceType)
        {
            List<ETLDbConfig> list = new List<ETLDbConfig>();
            var sourceType = Convert.ToInt32(collection["SourceType"]);

            JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
            IList<ETLDbInfo> jsonList = (IList<ETLDbInfo>)jsSerializer.Deserialize(collection["DBs"], typeof(IList<ETLDbInfo>));
            foreach (ETLDbInfo dbInfo in jsonList)
            {
                string dataSource = dbInfo.Host;
                string database = dbInfo.Server;
                string userID = dbInfo.User;
                string password = dbInfo.Pass;
                string port = dbInfo.Part ;
                string sid = dbInfo.SID;
                string wildCard = dbInfo.WildCard;
                int extractOrder = dbInfo.ExtractOrder;
                string strCon = string.Empty;

                switch (sourceType)
                {
                    case 1:
                        strCon = string.Format(PublicController.SQLSERVER_FRAMAT, dataSource, database, userID, password, port, sid);
                        break;
                    case 2:
                        strCon = string.Format(PublicController.ORACLE_FRAMAT, dataSource, database, userID, password, port, sid);
                        if (dataSource.IndexOf("|") > -1)
                        {
                            List<string> hostArr = dataSource.Split('|').ToList();
                            List<string> portArr = dbInfo.Part.Split('|').ToList();
                            StringBuilder ora = new StringBuilder();
                            ora.Append("Data Source=(DESCRIPTION=");
                            for (int i = 0; i < hostArr.Count; i++)
                            {
                                ora.Append(string.Format("(ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = {1}))", hostArr[i], portArr[i]));
                            }
                            ora.Append(string.Format("(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = {0})));User Id={1};Password={2};", database, userID, password));
                            strCon = ora.ToString();
                        }
                        break;
                    case 4:
                    case 8:
                        strCon = string.Format(PublicController.MYSQL_FRAMAT, dataSource, database, userID, password, port, sid);
                        break;
                    case 3:
                    case 5:
                    case 6:
                    case 7:
                        dataSource = dbInfo.FileUrl;
                        userID = dbInfo.FileUser;
                        password = dbInfo.FilePass;
                        strCon = string.Format(PublicController.ACCESS_FRAMAT, dbInfo.FileUrl, dbInfo.FileUser, dbInfo.FilePass);
                        break;
                }
                list.Add(new ETLDbConfig()
                {
                    GUID = dbInfo.GUID,
                    DbDataSource = dataSource,
                    DbDatabase = database,
                    DbUserID = userID,
                    DbPassword = encrypCode.Encrypto(password),
                    DbPort = port,
                    DbSID = sid,
                    WildCard = wildCard,
                    ExtractOrder = extractOrder,
                    DbConnectionStr = encrypCode.Encrypto(strCon),
                    Description = dbInfo.Description
                });
            }
            return list;
        }
    }
}
